Explore data – peru-prices

Get data

Last execution time: 10/02/2025 05:41:25

Products type filter

explore_types = ['frutas', 'lacteos', 'verduras', 'embutidos', 'panaderia', 'desayuno', 'congelados', 'abarrotes',
                 'aves', 'carnes', 'pescados']

Data table

path = Path('../../output')
csv_files = L(path.glob('*.csv')).filter(lambda o: os.stat(o).st_size>0)
pat_store = re.compile('(.+)\_\d+')
pat_date = re.compile('.+\_(\d+)')
df = (
    pd.concat([pd.read_csv(o).assign(store=pat_store.match(o.stem)[1], date=pat_date.match(o.stem)[1])
               for o in csv_files], ignore_index=True)
    .pipe(lambda d: d.assign(
        name=d.name.str.lower()+' ('+d.store+')',
        sku=d.id.where(d.sku.isna(), d.sku).astype(int),
        date=pd.to_datetime(d.date)
    ))
    .drop('id', axis=1)
    .loc[lambda d: d.category.str.contains('|'.join(explore_types))]
    # Filter products with recent data
#     .loc[lambda d: d.name.isin(d.groupby('name').date.max().loc[ge(datetime.now()-timedelta(days=30))].index)]
    # Filter empty prices
    .loc[lambda d: d.price>0]
)
print(df.shape)
df.sample(3)

(1251362, 8)

	sku	name	brand	category	uri	price	store	date
2065703	10215251	yogurt piamonte café botella 340ml (plaza_vea)	PIAMONTE	https://www.plazavea.com.pe/lacteos-y-huevos	https://www.plazavea.com.pe/yogurt-piamonte-ca...	4.39	plaza_vea	2023-10-12
65838	966784	chorizo parrillero finas hierbas san fernando ...	San Fernando	https://www.metro.pe/embutidos-y-fiambres/embu...	https://www.metro.pe/chorizo-parrillero-finas-...	18.70	metro	2023-05-29
2595822	11668	mermelada la veronica sauco sin azúcar frasco ...	LA VERÓNICA	https://www.plazavea.com.pe/desayunos	https://www.plazavea.com.pe/mermelada-la-veron...	9.90	plaza_vea	2024-11-14

Top changes (ratio)

Code

top_changes = (df
 # Use last 30 days of data to compare prices
 .loc[lambda d: d.date>=(datetime.now()-timedelta(days=30))]
 .sort_values('date')
 # Get percentage change
 .assign(change=lambda d: d
     .groupby(['store','sku'], as_index=False)
     .price.transform(lambda d: (d-d.shift())/d.shift())
 )
 .groupby(['store','sku'], as_index=False)
 .agg({'price':'last', 'change':'mean', 'date':'last'})
 .rename({'price':'last_price', 'date':'last_date'}, axis=1)
 .dropna()
 .loc[lambda d: d.last_date==d.last_date.max()]
 .loc[lambda d: d.change.abs().sort_values(ascending=False).index]
)
top_changes.head(3)

	store	sku	last_price	change	last_date
5629	plaza_vea	11577781	8.3	0.982143	2025-02-10
5627	plaza_vea	11577779	8.3	0.982143	2025-02-10
5630	plaza_vea	11577782	8.3	0.982143	2025-02-10

Code

def plot_changes(df_changes, title):
    selection = alt.selection_point(fields=['name'], bind='legend')
    dff = df_changes.drop('change', axis=1).merge(df, on=['store','sku'])
    return (dff
     .pipe(alt.Chart)
     .mark_line(point=True)
     .encode(
         x='date',
         y='price',
         color=alt.Color('name').scale(domain=sorted(dff.name.unique().tolist())),
         tooltip=['name','price','last_price']
     )
     .add_params(selection)
     .transform_filter(selection)
     .interactive()
     .properties(width=650, title=title)
     .configure_legend(orient='top', columns=3)
    )

Code

top_changes.head(10).pipe(plot_changes, 'Top changes')

Code

(top_changes
 .sort_values('change')
 .head(10)
 .pipe(plot_changes, 'Top drops')
)

Code

(top_changes
 .sort_values('change')
 .tail(10)
 .pipe(plot_changes, 'Top increases')
)

Top changes (absolute values)

Code

top_changes_abs = (df
 # Use last 30 days of data to compare prices
 .loc[lambda d: d.date>=(datetime.now()-timedelta(days=30))]
 .sort_values('date')
 # Get percentage change
 .assign(change=lambda d: d
     .groupby(['store','sku'], as_index=False)
     .price.transform(lambda d: (d-d.shift()).iloc[-1])
 )
 .groupby(['store','sku'], as_index=False)
 .agg({'price':'last', 'change':'mean', 'date':'last'})
 .rename({'price':'last_price', 'date':'last_date'}, axis=1)
 .dropna()
 .loc[lambda d: d.last_date==d.last_date.max()]
 .loc[lambda d: d.change.abs().sort_values(ascending=False).index]
)
top_changes_abs.head(3)

	store	sku	last_price	change	last_date
1349	plaza_vea	12706	55.9	-17.1	2025-02-10
5593	plaza_vea	11566870	45.0	13.8	2025-02-10
5049	plaza_vea	11429645	65.9	13.0	2025-02-10

Code

top_changes_abs.head(10).pipe(plot_changes, 'Top changes')

Code

(top_changes_abs
 .sort_values('change')
 .head(10)
 .pipe(plot_changes, 'Top drops')
)

Code

(top_changes_abs
 .sort_values('change')
 .tail(10)
 .pipe(plot_changes, 'Top increases')
)

Search specific products

Code

(df
 .loc[df.name.isin(names)]
 .pipe(alt.Chart)
 .mark_line(point=True)
 .encode(x='date', y='price', color='name', tooltip=['name','price'])
 .properties(width=650, title='Pollo')
 .interactive()
 .configure_legend(orient='top', columns=3)
)

Code

(df
 .loc[df.name.isin(names)]
 .pipe(alt.Chart)
 .mark_line(point=True)
 .encode(x='date', y='price', color='name', tooltip=['name','price'])
 .properties(width=650, title='Palta')
 .interactive()
 .configure_legend(orient='top', columns=3)
)

Code

(df
 .loc[df.name.isin(names)]
 .pipe(alt.Chart)
 .mark_line(point=True)
 .encode(x='date', y='price', color='name', tooltip=['name','price'])
 .properties(width=650, title='Aceite')
 .interactive()
 .configure_legend(orient='top', columns=3)
)

Code

(df
 .loc[df.name.isin(names)]
 .pipe(alt.Chart)
 .mark_line(point=True)
 .encode(x='date', y='price', color='name', tooltip=['name','price'])
 .properties(width=650, title='Aceite')
 .interactive()
 .configure_legend(orient='top', columns=3)
)

Code

(df
 .loc[df.name.isin(names)]
 .pipe(alt.Chart)
 .mark_line(point=True)
 .encode(x='date', y='price', color='name', tooltip=['name','price'])
 .properties(width=650, title='Aceite')
 .interactive()
 .configure_legend(orient='top', columns=3)
)